package com.yzq.os.spider.v.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.yzq.os.spider.v.dao.mapper.QueryURLMapper;
import com.yzq.os.spider.v.domain.QueryURL;

@Repository
public class QueryURLDao extends AbstractDao {

	public void batchSave(String tableName, final List<QueryURL> urls) {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into ");
		sql.append(tableName);
		sql.append(" (search_engine_id, spell_url, post_url, do_flag) values (?, ?, ?, ?)");

		jdbcTemplateOnline.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				QueryURL url = urls.get(index);
				ps.setInt(1, url.getSearchEngineId());
				ps.setString(2, url.getSpellUrl());
				ps.setString(3, url.getPostUrl());
				ps.setInt(4, url.getDoFlag());
			}

			@Override
			public int getBatchSize() {
				return urls.size();
			}
		});
	}

	public List<QueryURL> find(String tableName, int searchEngineId, int doFlag, int maxReturn) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select t.id, t.search_engine_id, t.spell_url, t.post_url, t.do_flag ");
		sql.append("   from " + tableName + " t ");
		sql.append("  where t.search_engine_id = ? ");
		sql.append("    and t.do_flag = ? limit 0, ? for update ");

		return jdbcTemplateOnline.query(sql.toString(), new Object[] { searchEngineId, doFlag, maxReturn }, new QueryURLMapper());
	}

	public void batchUpdateDoFlag(String tableName, final List<QueryURL> urls) {
		StringBuffer sql = new StringBuffer();
		sql.append("update ");
		sql.append(tableName);
		sql.append(" t set t.do_flag = ? where t.id = ?");

		jdbcTemplateOnline.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				QueryURL url = urls.get(index);
				ps.setInt(1, url.getDoFlag());
				ps.setInt(2, url.getId());
			}

			@Override
			public int getBatchSize() {
				return urls.size();
			}
		});
	}

	public void truncate(String tableName) {
		String sql = " truncate table " + tableName;
		logger.info("SQL:[" + sql + "]");
		jdbcTemplateOnline.execute(sql);
	}

	public List<String[]> procStatistics(final int searchEngineId, final String tableName) {
		if (isExistTableOnlineDatabase(tableName)) {
			StringBuffer sql = new StringBuffer();
			sql.append(" select search_engine_id, do_flag, count(*) count_url ");
			sql.append("   from " + tableName + " ");
			sql.append("  group by search_engine_id, do_flag ");
			sql.append("  order by search_engine_id, do_flag ");

			logger.debug("SQL:[" + sql.toString() + "]");

			return jdbcTemplateOnline.query(sql.toString(), new RowMapper<String[]>() {

				@Override
				public String[] mapRow(ResultSet rs, int index) throws SQLException {

					int doFlag = rs.getInt("do_flag");
					int count = rs.getInt("count_url");

					String[] strs = new String[4];
					strs[0] = tableName;
					strs[1] = String.valueOf(searchEngineId);
					strs[2] = String.valueOf(doFlag);
					strs[3] = String.valueOf(count);

					return strs;
				}
			});
		}
		return null;
	}

	public void initValidBakQueryUrls(int searchEngineId, String tableName) {
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into " + tableName + " ");
		sql.append("   (search_engine_id, spell_url, post_url, do_flag) ");
		sql.append("   select search_engine_id, spell_url, post_url, do_flag ");
		sql.append("     from init_query_url_bak where search_engine_id = ?");

		logger.info("SQL:[" + sql.toString() + "] searchEngineId:[" + searchEngineId + "]");

		jdbcTemplateOnline.update(sql.toString(), searchEngineId);
	}

	public void createTable(String tableName) {
		StringBuffer sql = new StringBuffer();
		sql.append(" CREATE TABLE `" + tableName + "` ( ");
		sql.append("   `id` int(11) NOT NULL auto_increment, ");
		sql.append("   `search_engine_id` int(11) NOT NULL, ");
		sql.append("   `spell_url` varchar(2048) NOT NULL, ");
		sql.append("   `post_url` varchar(2048) NOT NULL, ");
		sql.append("   `do_flag` int(11) NOT NULL default '0' COMMENT '0:wait\n1:doing\n2:ok\n3:error\n', ");
		sql.append("   PRIMARY KEY  (`id`), ");
		sql.append("   KEY `ENGINE_ID_DO_FLAG` (`search_engine_id`,`do_flag`) ");
		sql.append(" ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ");

		logger.info("Create query url table[" + tableName + "]. SQL:[" + sql.toString() + "]");
		jdbcTemplateOnline.execute(sql.toString());
	}

	public void batchSaveToBackTable(final int searchEngineId, final String[] urls) {
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO init_query_url_bak ");
		sql.append("   (search_engine_id, spell_url, post_url, do_flag) ");
		sql.append(" VALUES ");
		sql.append("   (?, ?, ?, ?) ");

		jdbcTemplateOnline.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				ps.setInt(1, searchEngineId);
				ps.setString(2, urls[index]);
				ps.setString(3, urls[index]);
				ps.setInt(4, 0);
			}

			@Override
			public int getBatchSize() {
				return urls.length;
			}
		});
	}

	public void batchSaveToBackTable(String queryUrlTableName) {
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO init_query_url_bak ");
		sql.append("   (search_engine_id, spell_url, post_url, do_flag) ");
		sql.append("   SELECT search_engine_id, spell_url, post_url, do_flag FROM " + queryUrlTableName + " ");

		logger.info(sql.toString());

		jdbcTemplateOnline.update(sql.toString());
	}

	public void deleteValidBakQueryUrls(int searchEngineId) {
		String sql = " delete from init_query_url_bak where search_engine_id = ? ";
		jdbcTemplateOnline.update(sql, searchEngineId);
	}

	public List<String> findFirstValidBakQueryUrl(int searchEngineId, int maxReturn) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select post_url ");
		sql.append("   from init_query_url_bak ");
		sql.append("  where search_engine_id = ? limit 0, ? ");

		return jdbcTemplateOnline.queryForList(sql.toString(), new Object[] { searchEngineId, maxReturn }, String.class);
	}

}
